package com.lecture.java8.excel.a0210;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.lecture.java8.excel.DemoDataListener;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.*;
import java.util.stream.Collectors;

public class Main {
    static String path = "D:\\study\\tmp\\25-0207\\";
    static DecimalFormat df = new DecimalFormat("#.00");

    public static void main3(String[] args) {
        Main main = new Main();
        List<Sheet2> sheet4List = main.sheet2();
        EasyExcel.write("D:\\study\\tmp\\25-0207\\0321—1结果.xlsx", Sheet2.class)
                .sheet("a").doWrite(sheet4List);
    }

    public static void main(String[] args) {
        Main main = new Main();
        /*List<Sheet4> sheet4List = main.sheet4();
        EasyExcel.write("D:\\study\\tmp\\25-0207\\0321—1结果.xlsx", Sheet4.class)
                .sheet("a").doWrite(sheet4List);*/
       /* List<Sheet2> sheet2List = main.sheet2();
        EasyExcel.write("D:\\study\\tmp\\25-0207\\0321—1结果.xlsx", Sheet2.class)
                .sheet("a").doWrite(sheet2List);*/

        String template = path + "template2.xlsx";
        String fileName = path + "结果.xlsx";


        try  {
            List<Sheet1_1> data1 = main.getSheet1_1List();
            List<Sheet1_2> data2 = main.getSheet1_2();
            List<Sheet1_3> data3 = main.getSheet1_3();
            List<Sheet1_4> data4 = main.getSheet1_4();
            List<Sheet2> sheet2List = main.sheet2();
            List<Entity2> sheet3 = main.sheet3();
            List<Sheet4> sheet4List = main.sheet4();


            ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(template).build();


            // 创建第一个 sheet
            WriteSheet writeSheet1 = EasyExcel.writerSheet(0).sheetName("输出报表").build();  // 第一个 sheet，索引为 0
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();

            // 填充第一个 sheet 的数据
            excelWriter.fill(new FillWrapper("data1", data1), fillConfig, writeSheet1);
            excelWriter.fill(new FillWrapper("data2", data2), fillConfig, writeSheet1);
            excelWriter.fill(new FillWrapper("data3", data3), fillConfig, writeSheet1);
            excelWriter.fill(new FillWrapper("data4", data4), fillConfig, writeSheet1);

            // 创建第二个 sheet（填充实体类数据）
            WriteSheet writeSheet2 = EasyExcel.writerSheet(1).sheetName("输出明细-项目时效").head(Sheet2.class).build();  // 第二个 sheet，索引为 1

            // 假设实体类为 EntityClass，data4 是包含该实体类列表的数据
            excelWriter.write(sheet2List, writeSheet2);

            // 创建第二个 sheet（填充实体类数据）
            WriteSheet writeSheet3 = EasyExcel.writerSheet(2).sheetName("输出明细-签约额").head(Entity2.class).build();  // 第二个 sheet，索引为 1

            // 假设实体类为 EntityClass，data4 是包含该实体类列表的数据
            excelWriter.write(sheet3, writeSheet3);

            // 创建第二个 sheet（填充实体类数据）
            WriteSheet writeSheet4 = EasyExcel.writerSheet(3).sheetName("输出明细-中标额").head(Sheet4.class).build();  // 第二个 sheet，索引为 1

            // 假设实体类为 EntityClass，data4 是包含该实体类列表的数据
            excelWriter.write(sheet4List, writeSheet4);

            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<Sheet1_4> getSheet1_4() {
        List<Sheet1_4> resultList = new ArrayList<>();
        List<Entity2> sheet3List = sheet3();
        List<Sheet4> sheet4List = sheet4();
        Map<String, List<Entity2>> sheet3Map = sheet3List.stream()
                .filter(item -> item.getIndustry() != null)
                .collect(Collectors.groupingBy(Entity2::getIndustry));

        Map<String, List<Sheet4>> sheet4Map = sheet4List.stream()
                .filter(item -> item.getPrimaryIndustry() != null)
                .collect(Collectors.groupingBy(Sheet4::getPrimaryIndustry));
        sheet3Map.forEach((industry,sheet3ListTmp) -> {

            Sheet1_4 sheet14 = new Sheet1_4();

            //行业
            sheet14.setIndustry(industry);

            //签约额（万）
            double contractAmount = sheet3ListTmp.stream()
                    .filter(item -> item.getContractAmount() != null)
                    .mapToDouble(Entity2::getContractAmount)
                    .sum();
            sheet14.setContractAmount(contractAmount);


            //签约个数
            sheet14.setContractCount(sheet3ListTmp.size());

            //中标额（万）
            List<Sheet4> sheet4ListTmp = sheet4Map.get(industry);
            if (sheet4ListTmp != null) {
                double winningAmount = sheet4ListTmp.stream()
                        .filter(item -> item.getBidAmount() != null)
                        .mapToDouble(Sheet4::getBidAmount)
                        .sum();
                sheet14.setWinningAmount(winningAmount);

                sheet14.setWinningCount(sheet4ListTmp.size());
            }
            resultList.add(sheet14);
        });

        Sheet1_4 sheet14 = new Sheet1_4();
        sheet14.setIndustry("合计");

        // 计算各字段的合
        double contractAmount = resultList.stream()
                .mapToDouble(Sheet1_4::getContractAmount)
                .sum();

        int contractCount = resultList.stream()
                .mapToInt(Sheet1_4::getContractCount)
                .sum();

        double winningAmount = resultList.stream()
                .mapToDouble(item -> item.getWinningAmount() != null ? item.getWinningAmount() : 0.0)
                .sum();

        int winningCount = resultList.stream()
                .mapToInt(item -> item.getWinningCount() != null ? item.getWinningCount() : 0)
                .sum();

        // 使用 BigDecimal 格式化结果并保留两位小数
        contractAmount = new BigDecimal(contractAmount).setScale(2, RoundingMode.HALF_UP).doubleValue();
        winningAmount = new BigDecimal(winningAmount).setScale(2, RoundingMode.HALF_UP).doubleValue();

        sheet14.setContractAmount(contractAmount);
        sheet14.setContractCount(contractCount);
        sheet14.setWinningAmount(winningAmount);
        sheet14.setWinningCount(winningCount);
        resultList.add(sheet14);

        return resultList;
    }

    public List<Sheet1_3> getSheet1_3() {
        List<Entity2> sheet3List = sheet3();
        List<Sheet4> sheet4List = sheet4();
        List<Sheet1_3> resultList = new ArrayList<>();
        //根据区县分组
        Map<String, List<Entity2>> map = sheet3List
                .stream()
                .filter(item -> item.getDistrict() != null && !"".equals(item.getDistrict()))
                .collect(Collectors.groupingBy(Entity2::getDistrict));
        Map<String, List<Sheet4>> map4 = sheet4List
                .stream()
                .filter(item -> item.getDistrict() != null && !"".equals(item.getDistrict()))
                .collect(Collectors.groupingBy(Sheet4::getDistrict));
        map.forEach((district,sheet3ListTmp) -> {
            Sheet1_3 sheet13 = new Sheet1_3();
            sheet13.setDistrict(district);

            //签约额（万）
            double signedAmount = sheet3ListTmp.stream()
                    .filter(item -> item.getContractAmount() != null)
                    .mapToDouble(Entity2::getContractAmount)
                    .sum();
            sheet13.setSignedAmount(df.format(signedAmount));

            //签约额项目数
            sheet13.setSignedAmountProjectCount(sheet3ListTmp.size());

            //IT通服签约额
            double itTelecomSignedAmount = sheet3ListTmp.stream()
                    .filter(item -> item.getItServiceContractAmount() != null)
                    .mapToDouble(Entity2::getItServiceContractAmount)
                    .sum();
            sheet13.setItTelecomSignedAmount(itTelecomSignedAmount);

            //低毛利率（毛利率<5%)项目个数
            //TODO 去重
            Map<String, List<Entity2>> collect = sheet3ListTmp.stream()
                    .filter(item -> item.getGrossProfitMargin() != null && !"".equals(item.getGrossProfitMargin()))
                    .filter(item -> Double.parseDouble(item.getGrossProfitMargin().replace("%", "")) < 5)
                    .collect(Collectors.groupingBy(Entity2::getProjectCode));
            sheet13.setLowMarginProjectCount(collect.size());

            //低毛项目占比
            long lowMarginProjectRatio = sheet13.getLowMarginProjectCount() / sheet3ListTmp.size();
            sheet13.setLowMarginProjectRatio(lowMarginProjectRatio+"%");


            //信息化收入
            double informatizationIncome = sheet3ListTmp.stream()
                    .filter(item -> item.getDigitalizationRevenue() != null)
                    .mapToDouble(Entity2::getDigitalizationRevenue)
                    .sum();
            sheet13.setInformatizationIncome(informatizationIncome);

            //落收比
            int revenueRate = (int) (informatizationIncome / signedAmount);
            sheet13.setRevenueRate(revenueRate+"%");

            //CT收入（万）
            double ctIncome = sheet3ListTmp.stream()
                    .filter(item -> item.getCtRevenue() != null)
                    .mapToDouble(Entity2::getCtRevenue)
                    .sum();
            sheet13.setCtIncome(ctIncome);

            //木本占比
            int woodRatio = (int) (ctIncome / informatizationIncome);
            sheet13.setWoodRatio(woodRatio+"%");

            //中标额项目数（个）
            List<Sheet4> sheet4List1 = map4.get(district);
            if (sheet4List1 != null) {
                sheet13.setBidAmountProjectCount(sheet4List1.size()+"");

                //中标额（万）
                double bidAmount = sheet4List1.stream()
                        .filter(item -> item.getBidAmount() != null)
                        .mapToDouble(Sheet4::getBidAmount)
                        .sum();
                sheet13.setBidAmount(bidAmount);

                // 预计核减项目数（个）
                long expectedDeductionProjectCount = sheet4List1.stream()
                        .filter(item -> item.getIsReduced() != null && !"".equals(item.getIsReduced()) && "是".equals(item.getIsReduced()))
                        .count();
                sheet13.setExpectedDeductionProjectCount(expectedDeductionProjectCount+"");


                //// 核减后个数
                long deductionProjectCount = sheet4List1.size() - expectedDeductionProjectCount;
                sheet13.setDeductionProjectCount(deductionProjectCount+"");

                // 核减项目金额（万）
                double deductionAmount = sheet4List1.stream()
                        .filter(item -> item.getIsReduced() != null && !"".equals(item.getIsReduced())
                                && "是".equals(item.getIsReduced())
                                && item.getBidAmount() != null)
                        .mapToDouble(Sheet4::getBidAmount)
                        .sum();
                sheet13.setDeductionAmount(deductionAmount);

                //预计核减后中标额（万）
                int expectedDeductionBidAmount = (int) (bidAmount - deductionAmount);
                sheet13.setExpectedDeductionBidAmount(expectedDeductionBidAmount+"");
            }

            resultList.add(sheet13);
        });

        Sheet1_3 sheet13 = new Sheet1_3();
        sheet13.setDistrict("总计");

        // 对各个属性求和
        double signedAmount = resultList.stream()
                .filter(item -> item.getSignedAmount() != null && !"".equals(item.getSignedAmount()))
                .mapToDouble(item -> Double.parseDouble(item.getSignedAmount()))
                .sum();

        int signedAmountProjectCount = resultList.stream()
                .filter(item -> item.getSignedAmountProjectCount() != null)
                .mapToInt(Sheet1_3::getSignedAmountProjectCount)
                .sum();

        double itTelecomSignedAmount = resultList.stream()
                .filter(item -> item.getItTelecomSignedAmount() != null)
                .mapToDouble(Sheet1_3::getItTelecomSignedAmount)
                .sum();

        int lowMarginProjectCount = resultList.stream()
                .filter(item -> item.getLowMarginProjectCount() != null)
                .mapToInt(Sheet1_3::getLowMarginProjectCount)
                .sum();

        double lowMarginProjectRatio = resultList.stream()
                .filter(item -> item.getLowMarginProjectRatio() != null && !"".equals(item.getLowMarginProjectRatio()))
                .mapToDouble(item -> Double.parseDouble(item.getLowMarginProjectRatio().replace("%","")))
                .sum();

        double informatizationIncome = resultList.stream()
                .filter(item -> item.getInformatizationIncome() != null)
                .mapToDouble(Sheet1_3::getInformatizationIncome)
                .sum();

        double revenueRate = resultList.stream()
                .filter(item -> item.getRevenueRate() != null && !"".equals(item.getRevenueRate()))
                .mapToDouble(item -> Double.parseDouble(item.getRevenueRate().replace("%","")))
                .sum();

        double ctIncome = resultList.stream()
                .filter(item -> item.getCtIncome() != null)
                .mapToDouble(Sheet1_3::getCtIncome)
                .sum();

        double woodRatio = resultList.stream()
                .filter(item -> item.getWoodRatio() != null && !"".equals(item.getWoodRatio()))
                .mapToDouble(item -> Double.parseDouble(item.getWoodRatio().replace("%","")))
                .sum();

        double bidAmountProjectCount = resultList.stream()
                .filter(item -> item.getBidAmountProjectCount() != null && !"".equals(item.getBidAmountProjectCount()))
                .mapToDouble(item -> Double.parseDouble(item.getBidAmountProjectCount()))
                .sum();

        double bidAmount = resultList.stream()
                .filter(item -> item.getBidAmount() != null)
                .mapToDouble(Sheet1_3::getBidAmount)
                .sum();

        double expectedDeductionProjectCount = resultList.stream()
                .filter(item -> item.getExpectedDeductionProjectCount() != null && !"".equals(item.getExpectedDeductionProjectCount()))
                .mapToDouble(item -> Double.parseDouble(item.getExpectedDeductionProjectCount()))
                .sum();

        double deductionAmount = resultList.stream()
                .filter(item -> item.getDeductionAmount() != null)
                .mapToDouble(Sheet1_3::getDeductionAmount)
                .sum();

        double deductionProjectCount = resultList.stream()
                .filter(item -> item.getDeductionProjectCount() != null && !"".equals(item.getDeductionProjectCount()))
                .mapToDouble(item -> Double.parseDouble(item.getDeductionProjectCount()))
                .sum();

        double expectedDeductionBidAmount = resultList.stream()
                .filter(item -> item.getExpectedDeductionBidAmount() != null && !"".equals(item.getExpectedDeductionBidAmount()))
                .mapToDouble(item -> Double.parseDouble(item.getExpectedDeductionBidAmount()))
                .sum();


        sheet13.setSignedAmount(String.valueOf(new BigDecimal(signedAmount).setScale(2, RoundingMode.HALF_UP).toString()));
        sheet13.setSignedAmountProjectCount(signedAmountProjectCount);
        sheet13.setItTelecomSignedAmount(itTelecomSignedAmount);
        sheet13.setLowMarginProjectCount(lowMarginProjectCount);
        sheet13.setLowMarginProjectRatio(String.valueOf(lowMarginProjectRatio));
        sheet13.setInformatizationIncome(informatizationIncome);
        sheet13.setRevenueRate(String.valueOf(revenueRate));
        sheet13.setCtIncome(ctIncome);
        sheet13.setWoodRatio(String.valueOf(woodRatio));
        sheet13.setBidAmountProjectCount(String.valueOf(bidAmountProjectCount));
        sheet13.setBidAmount(bidAmount);
        sheet13.setExpectedDeductionProjectCount(String.valueOf(expectedDeductionProjectCount));
        sheet13.setDeductionAmount(deductionAmount);
        sheet13.setDeductionProjectCount(String.valueOf(deductionProjectCount));
        sheet13.setExpectedDeductionBidAmount(String.valueOf(expectedDeductionBidAmount));
        resultList.add(sheet13);
        return resultList;
    }


    public List<Sheet1_2> getSheet1_2() {
        List<Sheet2> sheet2List = sheet2();
        List<Sheet1_2> resultList = new ArrayList<>();
        //根据区县分组
        Map<String, List<Sheet2>> map = sheet2List
                .stream()
                .filter(item -> item.getDistrict() != null && !"".equals(item.getDistrict()))
                .collect(Collectors.groupingBy(Sheet2::getDistrict));
        map.forEach((district,sheet2ListTmp) -> {
            Sheet1_2 sheet12 = new Sheet1_2();
            sheet12.setDistrict(district);
            double signedAmountReserve = sheet2ListTmp.stream()
                    .filter(item -> item.getProjectApprovalTime() != null && !"".equals(item.getProjectApprovalTime())
                            && (item.getForwardIncomeContractTime() == null || "".equals(item.getForwardIncomeContractTime()))
                            && item.getProjectApprovalIncome() != null)
                    .mapToDouble(Sheet2::getProjectApprovalIncome)
                    .sum();
            sheet12.setSignedAmountReserve(signedAmountReserve);
            double itTelecomAmountReserve = sheet2ListTmp.stream()
                    .filter(item -> item.getProjectApprovalTime() != null && !"".equals(item.getProjectApprovalTime())
                            && (item.getForwardIncomeContractTime() == null || "".equals(item.getForwardIncomeContractTime()))
                            && item.getProjectApprovalITCost() != null)
                    .mapToDouble(Sheet2::getProjectApprovalITCost)
                    .sum();
            sheet12.setItTelecomAmountReserve(itTelecomAmountReserve);
            resultList.add(sheet12);
        });

        Sheet1_2 sheet12 = new Sheet1_2();
        sheet12.setDistrict("总计");
        double signedAmountReserve = resultList.stream()
                .filter(item -> item.getSignedAmountReserve() != null)
                .mapToDouble(Sheet1_2::getSignedAmountReserve)  // 将 projectCount 转换为整数
                .sum();
        sheet12.setSignedAmountReserve(signedAmountReserve);

        double itTelecomAmountReserve = resultList.stream()
                .filter(item -> item.getItTelecomAmountReserve() != null)
                .mapToDouble(Sheet1_2::getItTelecomAmountReserve)  // 将 projectCount 转换为整数
                .sum();
        sheet12.setItTelecomAmountReserve(itTelecomAmountReserve);
        resultList.add(sheet12);
        return resultList;
    }

    public List<Sheet1_1> getSheet1_1List() {
        List<Sheet2> sheet2List = sheet2();
        List<Sheet1_1> resultList = new ArrayList<>();
        //根据区县分组
        Map<String, List<Sheet2>> map = sheet2List
                .stream()
                .filter(item -> item.getDistrict() != null && !"".equals(item.getDistrict()))
                .collect(Collectors.groupingBy(Sheet2::getDistrict));
        map.forEach((district,sheet2ListTmp) -> {
            Sheet1_1 sheet11 = new Sheet1_1();
            sheet11.setDistrict(district);
            // 项目个数
            sheet11.setProjectCount(sheet2ListTmp.size());
            // 已结束项目数
            long completedProjectCount = sheet2ListTmp.stream()
                    .filter(item -> item.getBackwardContractTime() != null && !"".equals(item.getBackwardContractTime()))
                    .count();
            sheet11.setCompletedProjectCount(completedProjectCount);
            // 已结束项目平均时长
            int count = 0;
            int sum = 0;
            for (Sheet2 sheet2 : sheet2ListTmp) {
                if (sheet2.getBackwardContractTime() != null && !"".equals(sheet2.getBackwardContractTime())
                        && sheet2.getTotalDuration() != null && !"".equals(sheet2.getTotalDuration())) {
                    count++;
                    sum += Integer.parseInt(sheet2.getTotalDuration());
                }
            }
            if (sum > 0) {
                int avgCompletedProjectDuration = sum / count;
                sheet11.setAvgCompletedProjectDuration(avgCompletedProjectDuration);
            }


            // 未结束项目数
            if (sheet11.getProjectCount() != null  && sheet11.getCompletedProjectCount() != null) {
                int ongoingProjectCount = (int) (sheet11.getProjectCount() - sheet11.getCompletedProjectCount());
                sheet11.setOngoingProjectCount(ongoingProjectCount);
            }

            // 已签约前向合同项目数
            long signedForwardContractProjectCount = sheet2ListTmp.stream()
                    .filter(item -> item.getForwardIncomeContractTime() != null && !"".equals(item.getForwardIncomeContractTime()))
                    .count();
            sheet11.setSignedForwardContractProjectCount(signedForwardContractProjectCount);

            // 中标未及时立项项目数
            long delayedProjectCount = sheet2ListTmp.stream()
                    .filter(item -> item.getProjectSubmissionAfterBidTime() != null
                            && !"".equals(item.getProjectSubmissionAfterBidTime())
                            && Integer.parseInt(item.getProjectSubmissionAfterBidTime()) > 5)
                    .count();
            sheet11.setDelayedProjectCount(delayedProjectCount);

            //中标未及时立项项目占比
            sheet11.setDelayedProjectRatio(delayedProjectCount / sheet2ListTmp.size() + "%");

            //签约未及时发起转售中项目数
            long delayedResaleProjectCount = sheet2ListTmp.stream()
                    .filter(item -> item.getResaleProcessDuration() != null
                            && !"".equals(item.getResaleProcessDuration())
                            && Integer.parseInt(item.getResaleProcessDuration()) > 5)
                    .count();
            sheet11.setDelayedResaleProjectCount(delayedResaleProjectCount);

            //签约未及时发起转售中项目占比
            sheet11.setDelayedResaleProjectRatio(delayedResaleProjectCount / signedForwardContractProjectCount + "%");

            //已转售中项目数
            long resaleInProgressProjectCount = sheet2ListTmp.stream()
                    .filter(item -> item.getResaleTime() != null
                            && !"".equals(item.getResaleTime()))
                    .count();
            sheet11.setResaleInProgressProjectCount(resaleInProgressProjectCount);

            //已转售中项目占比
            sheet11.setResaleInProgressProjectRatio(resaleInProgressProjectCount / signedForwardContractProjectCount + "%");
            resultList.add(sheet11);
        });
        Sheet1_1 sheet11 = new Sheet1_1();
        sheet11.setDistrict("总计");
        long projectCount = resultList.stream()
                .filter(item -> item.getProjectCount() != null)
                .mapToInt(Sheet1_1::getProjectCount)  // 将 projectCount 转换为整数
                .sum();
        sheet11.setProjectCount((int) projectCount);


        // 对各个属性进行求和
        int completedProjectCount = resultList.stream()
                .filter(item -> item.getCompletedProjectCount() != null)
                .mapToInt(item -> item.getCompletedProjectCount().intValue())
                .sum();

        int avgCompletedProjectDuration = resultList.stream()
                .filter(item -> item.getAvgCompletedProjectDuration() != null)
                .mapToInt(Sheet1_1::getAvgCompletedProjectDuration)
                .sum();

        int ongoingProjectCount = resultList.stream()
                .filter(item -> item.getOngoingProjectCount() != null)
                .mapToInt(Sheet1_1::getOngoingProjectCount)
                .sum();

        int signedForwardContractProjectCount = resultList.stream()
                .filter(item -> item.getSignedForwardContractProjectCount() != null)
                .mapToInt(item -> item.getSignedForwardContractProjectCount().intValue())
                .sum();

        int delayedProjectCount = resultList.stream()
                .filter(item -> item.getDelayedProjectCount() != null)
                .mapToInt(item -> item.getDelayedProjectCount().intValue())
                .sum();

        int delayedProjectRatio = resultList.stream()
                .filter(item -> item.getDelayedProjectRatio() != null && !"".equals(item.getDelayedProjectRatio()))
                .mapToInt(item -> Integer.parseInt(item.getDelayedProjectRatio().replace("%","")))
                .sum();

        int delayedResaleProjectCount = resultList.stream()
                .filter(item -> item.getDelayedResaleProjectCount() != null)
                .mapToInt(item -> item.getDelayedResaleProjectCount().intValue())
                .sum();

        int delayedResaleProjectRatio = resultList.stream()
                .filter(item -> item.getDelayedResaleProjectRatio() != null && !"".equals(item.getDelayedResaleProjectRatio()))
                .mapToInt(item -> Integer.parseInt(item.getDelayedResaleProjectRatio().replace("%","")))
                .sum();

        int resaleInProgressProjectCount = resultList.stream()
                .filter(item -> item.getResaleInProgressProjectCount() != null)
                .mapToInt(item -> item.getResaleInProgressProjectCount().intValue())
                .sum();

        int resaleInProgressProjectRatio = resultList.stream()
                .filter(item -> item.getResaleInProgressProjectRatio() != null && !"".equals(item.getResaleInProgressProjectRatio()))
                .mapToInt(item -> Integer.parseInt(item.getResaleInProgressProjectRatio().replace("%","")))
                .sum();
        // 将结果设置回 sheet11 对象
        sheet11.setCompletedProjectCount((long) completedProjectCount);
        sheet11.setAvgCompletedProjectDuration(avgCompletedProjectDuration);
        sheet11.setOngoingProjectCount(ongoingProjectCount);
        sheet11.setSignedForwardContractProjectCount((long) signedForwardContractProjectCount);
        sheet11.setDelayedProjectCount((long) delayedProjectCount);
        sheet11.setDelayedProjectRatio(String.valueOf(delayedProjectRatio));
        sheet11.setDelayedResaleProjectCount((long) delayedResaleProjectCount);
        sheet11.setDelayedResaleProjectRatio(String.valueOf(delayedResaleProjectRatio));
        sheet11.setResaleInProgressProjectCount((long) resaleInProgressProjectCount);
        sheet11.setResaleInProgressProjectRatio(String.valueOf(resaleInProgressProjectRatio));
        resultList.add(sheet11);
        return resultList;
    }


    public List<Sheet2> sheet2() {
        List<Sheet2> sheet2List = new ArrayList<>();
        List<Entity5> entity5List = getEntity5List();
        Map<String, Entity1> entity1Map = getEntityMap();
        Map<String, Entity4> entity4Map = getEntity4Map();
        Map<String, Entity4_2> entity42Map = getEntity4_2();
        for (Entity5 entity5 : entity5List) {
            Sheet2 sheet2 = new Sheet2();
            sheet2.setDistrict(entity5.getDistrict());
            sheet2.setProjectName(entity5.getProjectName());
            sheet2.setProjectCode(entity5.getProjectCode());
            sheet2.setGlobalProjectCode(entity5.getGlobalProjectCode());
            sheet2.setGroupCustomerName(entity5.getGroupCustomerName());
            sheet2.setProjectStage(entity5.getProjectStage());
            sheet2.setProjectStatus(entity5.getProjectStatus());
            sheet2.setIsBidding(entity5.getIsBidding());
            //1-项目明细报表，根据项目编码获取项目明细
            Entity1 entity1 = entity1Map.get(sheet2.getProjectCode());
            Entity4 entity4 = entity4Map.get(sheet2.getProjectCode());
            Entity4_2 entity42 = entity42Map.get(sheet2.getProjectCode());
            if (entity1 != null) {
                //合作模式 = entity1.cooperationMode
                sheet2.setCooperationMode(entity1.getCooperationMode());
                //发起转售中时间 = entity1.转售中发起时间
                sheet2.setResaleInitiationTime(entity1.getResaleStartTime());
            }
            if (entity4 != null) {
                //项目呈批收入（不含受托代销）（元） = entity1.approvalIncome
                sheet2.setProjectApprovalIncome(entity4.getApprovalIncome());
                //项目呈批支出金额（不含受托代销）（元） = entity4.呈批成本
                sheet2.setProjectApprovalExpenditure(entity4.getApprovalCost());
            }
            if (entity42 != null) {
                //项目呈批IT通服金额（元） = entity4.集成费收入 + entity4.ICT维保费收入 + entity4.集成费收入 第三个sheet页
                double integrationIncomeD = 0.0D;
                double ictMaintenanceIncomeD = 0.0D;
                double serviceFeeIncomeD = 0.0D;
                String integrationIncome = entity42.getIntegrationIncome();
                if (integrationIncome != null && !"".equals(integrationIncome)) {
                    integrationIncomeD = Double.parseDouble(integrationIncome);
                }
                String ictMaintenanceIncome = entity42.getIctMaintenanceIncome();
                if (ictMaintenanceIncome != null && !"".equals(ictMaintenanceIncome)) {
                    ictMaintenanceIncomeD = Double.parseDouble(ictMaintenanceIncome);
                }
                String serviceFeeIncome = entity42.getServiceFeeIncome();
                if (serviceFeeIncome != null && !"".equals(serviceFeeIncome)) {
                    serviceFeeIncomeD = Double.parseDouble(serviceFeeIncome);
                }
                double projectApprovalITCost = integrationIncomeD + ictMaintenanceIncomeD + serviceFeeIncomeD;
                sheet2.setProjectApprovalITCost(projectApprovalITCost);
            }
            //项目中标时间 = entity5.项目中标时间
            sheet2.setProjectBidTime(entity5.getProjectWinningDate());
            //立项提交时间 = entity5.立项提交时间
            sheet2.setProjectSubmissionTime(entity5.getProjectApprovalSubmissionDate());
            //立项审核通过时间 = entity5.立项审核通过时间
            sheet2.setProjectApprovalTime(entity5.getProjectApprovalPassDate());
            //前向收入合同签订时间 = entity5.第一份收入类合同签订时间
            sheet2.setForwardIncomeContractTime(entity5.getFirstRevenueContractSignDate());
            //前向收入金额（元）（含税） = entity5.前向签约金额（含税）
            sheet2.setForwardIncomeAmount(entity5.getForwardContractAmountWithTax());

            //转售中时间 = entity5.转售中时间
            sheet2.setResaleTime(entity5.getResalePeriodDate());
            //后向合同签订时间 = entity5.第一份支出类合同签订时间
            sheet2.setBackwardContractTime(entity5.getFirstExpenseContractSignDate());
            //后向成本金额（元）（含税） = entity5.后向签约金额不含税
            sheet2.setBackwardCostAmount(entity5.getBackwardContractAmountWithoutTax());
            //立项结束后发起前向合同时间 = R列有数据时需计算：①若S列有数据，则公式=S列-R列；②若S列无数据，则公式：今天-R列
            if (sheet2.getProjectApprovalTime() != null && !"".equals(sheet2.getProjectApprovalTime())) {
                DateTime r = DateUtil.parse(sheet2.getProjectApprovalTime());
                if (sheet2.getForwardIncomeContractTime() != null && !"".equals(sheet2.getForwardIncomeContractTime())) {
                    DateTime s = DateUtil.parse(sheet2.getForwardIncomeContractTime());
                    long between = DateUtil.between(s, r, DateUnit.DAY);
                    sheet2.setForwardContractInitiationTime(between+"");
                } else {
                    long between = DateUtil.between(new Date(), r, DateUnit.DAY);
                    sheet2.setForwardContractInitiationTime(between+"");
                }
            }
            //前向合同签订后发起转售中流程时长 = S列有数据时需计算：①若V列有数据，则公式=V列-S列；②若V列无数据，则公式=今天-S列
            if (sheet2.getForwardIncomeContractTime() != null && !"".equals(sheet2.getForwardIncomeContractTime())) {
                DateTime s = DateUtil.parse(sheet2.getForwardIncomeContractTime());
                if (sheet2.getResaleInitiationTime() != null && !"".equals(sheet2.getResaleInitiationTime())) {
                    DateTime v = DateUtil.parse(sheet2.getResaleInitiationTime());
                    long between = DateUtil.between(s, v, DateUnit.DAY);
                    sheet2.setResaleProcessDuration(between+"");
                } else {
                    long between = DateUtil.between(s, new Date(), DateUnit.DAY);
                    sheet2.setResaleProcessDuration(between+"");
                }
            }
            //中标-第一份合同签约时长 = 当S列有数据时需计算，①，O列有数据时，公式=S列-O列；②O列若为空，公式=S列-P列
            //sheet2.setFirstContractDuration();
            if (sheet2.getForwardIncomeContractTime() != null && !"".equals(sheet2.getForwardIncomeContractTime())) {
                DateTime s = DateUtil.parse(sheet2.getForwardIncomeContractTime());
                if (sheet2.getProjectBidTime() != null && !"".equals(sheet2.getProjectBidTime())) {
                    DateTime o = DateUtil.parse(sheet2.getProjectBidTime());
                    long between = DateUtil.between(s, o, DateUnit.DAY);
                    sheet2.setFirstContractDuration(between+"");
                } else if (sheet2.getProjectSubmissionTime() != null && !"".equals(sheet2.getProjectSubmissionTime())){
                    DateTime p = DateUtil.parse(sheet2.getProjectSubmissionTime());
                    long between = DateUtil.between(s, p, DateUnit.DAY);
                    sheet2.setFirstContractDuration(between+"");
                }
            }
            //总时长 = 当Y列有数据时需计算，①O列有数据，公式=Y列-O列；②O列若为空，公式=Y列-P列
            //sheet2.setTotalDuration();
            if (sheet2.getBackwardContractTime() != null && "".equals(sheet2.getBackwardContractTime())) {
                DateTime y = DateUtil.parse(sheet2.getBackwardContractTime());
                if (sheet2.getProjectBidTime() != null && !"".equals(sheet2.getProjectBidTime())) {
                    DateTime o = DateUtil.parse(sheet2.getProjectBidTime());
                    long between = DateUtil.between(y, o, DateUnit.DAY);
                    sheet2.setTotalDuration(between+"");
                } else {
                    DateTime p = DateUtil.parse(sheet2.getProjectSubmissionTime());
                    long between = DateUtil.between(y, p, DateUnit.DAY);
                    sheet2.setTotalDuration(between+"");
                }
            }
            //中标后提交立项时间 = ①O、P列都有数据时，公式=P列-O列；②O列无数据，无需计算；③O列有数据，P列无数据，则公式：今天-O列
            if (sheet2.getProjectBidTime() != null && !"".equals(sheet2.getProjectBidTime())
                    && sheet2.getProjectSubmissionTime() != null && !"".equals(sheet2.getProjectSubmissionTime())) {
                DateTime o = DateUtil.parse(sheet2.getProjectBidTime());
                DateTime p = DateUtil.parse(sheet2.getProjectSubmissionTime());
                long between = DateUtil.between(o, p, DateUnit.DAY);
                sheet2.setProjectSubmissionAfterBidTime(between+"");
            } else if (sheet2.getProjectBidTime() != null && !"".equals(sheet2.getProjectBidTime())) {
                DateTime o = DateUtil.parse(sheet2.getProjectBidTime());
                long between = DateUtil.between(o, new Date(), DateUnit.DAY);
                sheet2.setProjectSubmissionAfterBidTime(between+"");
            }
            sheet2List.add(sheet2);
        }
        return sheet2List;
    }
    public List<Entity2> sheet3() {

        List<Entity2> entity2List = getEntity2List();
        Map<String, Entity6> entity6Map = getEntity6Map();
        Map<String,Entity4> map4 = getEntity4Map();
        Map<String, Entity4_2> entity42 = getEntity4_2();


        for (Entity2 entity2 : entity2List) {
            //IT通服签约额
            Double ictTraditionalRevenue = entity2.getIctTraditionalRevenue();
            Double ictCloudRevenue = entity2.getIctCloudRevenue();
            Double ict5GRevenue = entity2.getIct5GRevenue();
            Double ictAIRevenue = entity2.getIctAIRevenue();
            Double ictBigDataRevenue = entity2.getIctBigDataRevenue();
            Double ict9oneRevenue = entity2.getIct9oneRevenue();
            Double ictSecurityIntegrationFee = entity2.getIctSecurityIntegrationFee();
            Double handlingFee = entity2.getHandlingFee();
            Double otherRevenue = entity2.getOtherRevenue();
            // 使用 BigDecimal 计算总和并保留两位小数
            BigDecimal itServiceContractAmount = BigDecimal.ZERO;
            itServiceContractAmount = itServiceContractAmount.add(toBigDecimal(ictTraditionalRevenue));
            itServiceContractAmount = itServiceContractAmount.add(toBigDecimal(ictCloudRevenue));
            itServiceContractAmount = itServiceContractAmount.add(toBigDecimal(ict5GRevenue));
            itServiceContractAmount = itServiceContractAmount.add(toBigDecimal(ictAIRevenue));
            itServiceContractAmount = itServiceContractAmount.add(toBigDecimal(ictBigDataRevenue));
            itServiceContractAmount = itServiceContractAmount.add(toBigDecimal(ict9oneRevenue));
            itServiceContractAmount = itServiceContractAmount.add(toBigDecimal(ictSecurityIntegrationFee));
            itServiceContractAmount = itServiceContractAmount.add(toBigDecimal(handlingFee));
            itServiceContractAmount = itServiceContractAmount.add(toBigDecimal(otherRevenue));
            // 保留两位小数
            itServiceContractAmount = itServiceContractAmount.setScale(2, RoundingMode.HALF_UP);
            entity2.setItServiceContractAmount(itServiceContractAmount.doubleValue());



            //AA列
            //信息化收入 = N + AA
            Double dedicatedLineRevenue = entity2.getDedicatedLineRevenue();
            Double revenue5G = entity2.getRevenue5G();
            Double publicCloudRevenue = entity2.getPublicCloudRevenue();
            Double bigDataAIRevenue = entity2.getBigDataAIRevenue();
            Double otherCategoryRevenue = entity2.getOtherCategoryRevenue();
            // 使用 BigDecimal 进行精确计算
            BigDecimal digitalizationRevenue = BigDecimal.ZERO;
            // 累加所有收入属性
            digitalizationRevenue = digitalizationRevenue.add(toBigDecimal(ictTraditionalRevenue))
                    .add(toBigDecimal(ictCloudRevenue))
                    .add(toBigDecimal(ict5GRevenue))
                    .add(toBigDecimal(ictAIRevenue))
                    .add(toBigDecimal(ictBigDataRevenue))
                    .add(toBigDecimal(ict9oneRevenue))
                    .add(toBigDecimal(ictSecurityIntegrationFee))
                    .add(toBigDecimal(handlingFee))
                    .add(toBigDecimal(otherRevenue))
                    .add(toBigDecimal(dedicatedLineRevenue))
                    .add(toBigDecimal(revenue5G))
                    .add(toBigDecimal(publicCloudRevenue))
                    .add(toBigDecimal(bigDataAIRevenue))
                    .add(toBigDecimal(otherCategoryRevenue));
            // 保留两位小数
            digitalizationRevenue = digitalizationRevenue.setScale(2, RoundingMode.HALF_UP);
            entity2.setDigitalizationRevenue(digitalizationRevenue.doubleValue());


            Entity6 entity6 = entity6Map.get(entity2.getForwardContractCode());
            if (entity6 != null) {
                //客户经理 = 用 合同编码 匹配DICT系统合同明细报表里 客户名称（AS列）
                //匹配第6个excel 根据合同编号匹配，取“客户经理”列

                //客户单位 = 用 前向合同编码 匹配DICT系统合同明细报表里 客户名称（AS列）
                //匹配第6个excel 根据合同编号匹配，取“客户名称”列

                //集团客户编码 = 用 前向合同编码 匹配DICT系统合同明细报表里 客户名称（AR列）
                //匹配第6个excel 根据合同编号匹配，取“客户编号”列
                entity2.setCustomerUnit(entity6.getCustomerName());
                entity2.setCorporateCustomerCode(entity6.getCustomerId());
                entity2.setCustomerManager(entity6.getCustomerManager());
            }



            Entity4 entity4 = map4.get(entity2.getProjectCode());

            if (entity4 != null) {
                //S列
                BigDecimal totalRevenue = BigDecimal.ZERO;
                totalRevenue = totalRevenue
                        .add(toBigDecimal(entity2.getEntrustedAgencyRevenue()))
                        .add(toBigDecimal(entity4.getApprovalIncome()));
                entity2.setTotalRevenue(totalRevenue.doubleValue());


                BigDecimal totalExpenditure = BigDecimal.ZERO;
                totalExpenditure = totalExpenditure
                        .add(toBigDecimal(entity2.getEntrustedAgencyRevenue()))
                        .add(toBigDecimal(entity4.getApprovalCost()));
                entity2.setTotalExpenditure(totalExpenditure.doubleValue());

                if (totalRevenue.compareTo(BigDecimal.ZERO) != 0) {
                    BigDecimal result = totalRevenue.subtract(totalExpenditure).divide(totalRevenue, 2, RoundingMode.HALF_UP);
                    entity2.setGrossProfitMargin(result +"%");
                }

            }
            BigDecimal ctRevenue = BigDecimal.ZERO;
            ctRevenue = ctRevenue
                    .add(toBigDecimal(entity2.getDedicatedLineRevenue()))
                    .add(toBigDecimal(entity2.getRevenue5G()))
                    .add(toBigDecimal(entity2.getPublicCloudRevenue()))
                    .add(toBigDecimal(entity2.getBigDataAIRevenue()))
                    .add(toBigDecimal(entity2.getOtherCategoryRevenue()));
            ctRevenue = ctRevenue.setScale(2, RoundingMode.HALF_UP);
            entity2.setCtRevenue(ctRevenue.doubleValue());
        }
        return entity2List;
    }



    /**
     * 项目编码-项目明细表
     * @return {@code Map<String,Entity4> }
     */
    public Map<String,Entity1> getEntityMap() {
        String entityPath1 = path + "1-项目明细报表.xlsx";
        DemoDataListener<Entity1> dataResultListener1 = new DemoDataListener<>();
        EasyExcel.read(entityPath1, Entity1.class,dataResultListener1).sheet().doRead();
        List<Entity1> resultList4 = dataResultListener1.getDataList();
        Map<String,Entity1> map = new HashMap<>();
        for (Entity1 entity1 : resultList4) {
            map.put(entity1.getProjectCode(),entity1);
        }
        return map;
    }

    /**
     * 项目编码-项目情况统计
     * @return {@code Map<String,Entity4> }
     */
    public Map<String,Entity4> getEntity4Map() {
        String entityPath4 = path + "4-项目情况表导出.xlsx";
        DemoDataListener<Entity4> dataResultListener4 = new DemoDataListener<>();
        EasyExcel.read(entityPath4, Entity4.class,dataResultListener4).sheet(0).doRead();
        List<Entity4> resultList4 = dataResultListener4.getDataList();
        Map<String,Entity4> map = new HashMap<>();
        for (Entity4 entity4 : resultList4) {
            map.put(entity4.getProjectCode(),entity4);
        }
        return map;
    }

    public Map<String,Entity4_2> getEntity4_2() {
        String entityPath4_2 = path + "4-项目情况表导出.xlsx";
        DemoDataListener<Entity4_2> dataResultListener4_2 = new DemoDataListener<>();
        EasyExcel.read(entityPath4_2, Entity4_2.class,dataResultListener4_2).sheet(2).doRead();
        List<Entity4_2> resultList4_2 = dataResultListener4_2.getDataList();
        Map<String,Entity4_2> map = new HashMap<>();
        for (Entity4_2 entity4_2 : resultList4_2) {
            map.put(entity4_2.getProjectCode(),entity4_2);
        }
        return map;
    }

    public List<Sheet4> sheet4() {
        String entityPath3 = path + "3-售前中标明细报表.xlsx";
        DemoDataListener<Entity3> dataResultListener3 = new DemoDataListener<>();
        EasyExcel.read(entityPath3, Entity3.class,dataResultListener3).sheet().doRead();
        List<Entity3> resultList3 = dataResultListener3.getDataList();
        Map<String, Entity5> entity5Map = getEntity5Map();
        Map<String, Entity2> entity2Map = getEntity2Map();
        List<Sheet4> sheet4List = BeanUtil.copyToList(resultList3, Sheet4.class);
        for (Sheet4 sheet4 : sheet4List) {
            Entity5 entity5 = entity5Map.get(sheet4.getProjectCode());
            Entity2 entity2 = entity2Map.get(sheet4.getProjectCode());
            //发起呈批时间 = entity5.立项提交时间
            sheet4.setInitiateApprovalTime(entity5.getProjectApprovalSubmissionDate());

            //是否五天内发起呈批 = ①Z列有数据时，Z列-M列，大于5天填写是，小于等于5天填写否②Z列无数据时，今天-M列，大于5天填写是，小于等于5天填写否
            if (sheet4.getInitiateApprovalTime() != null && !"".equals(sheet4.getInitiateApprovalTime())) {
                DateTime initiateApprovalTime = DateUtil.parse(sheet4.getInitiateApprovalTime());
                DateTime bidDate = DateUtil.parse(sheet4.getBidDate());
                long between = DateUtil.between(bidDate, initiateApprovalTime, DateUnit.DAY);
                if (between > 5) {
                    sheet4.setIsApprovalWithinFiveDays("是");
                } else {
                    sheet4.setIsApprovalWithinFiveDays("否");
                }
            } else {
                DateTime bidDate = DateUtil.parse(sheet4.getBidDate());
                long between = DateUtil.between(new Date(), bidDate, DateUnit.DAY);
                if (between > 5) {
                    sheet4.setIsApprovalWithinFiveDays("是");
                } else {
                    sheet4.setIsApprovalWithinFiveDays("否");
                }
            }



            //合同编号（若已归档匹配签约额表，没有归档空着）
            //用项目编码（D列）匹配邮箱里的ICT项目融CT产品报表合同收入明细统计表-前向合同编码(客户-ICT,客户-移动)(F列)
            if (entity2 != null) {
                sheet4.setArchivedContractCode(entity2.getForwardContractCode());
            }

            //是否两个月内归档= 每月月初判断一次，如3月1日看1月中标所有项目是否归档

            //是否归档 = AB列如有合同编码，则填写是，若无合同编码填写否
            if (sheet4.getArchivedContractCode() != null && !"".equals(sheet4.getArchivedContractCode())) {
                sheet4.setIsArchived("是");
            } else {
                sheet4.setIsArchived("否");
            }
            //是否核减 = AA列与AC列任一为否，则核减，否则不核减
            if ("否".equals(sheet4.getIsApprovalWithinFiveDays()) || "否".equals(sheet4.getIsArchivedWithinTwoMonths())) {
                sheet4.setIsReduced("核减");
            } else {
                sheet4.setIsReduced("不核减");
            }
        }
        return sheet4List;
    }

    public Map<String,Entity2> getEntity2Map() {
        List<Entity2> entity5List = getEntity2List();
        Map<String,Entity2> map = new HashMap<>();
        for (Entity2 entity2 : entity5List) {
            map.put(entity2.getProjectCode(),entity2);
        }
        return map;
    }

    public List<Entity2> getEntity2List() {
        String entityPath2 =path + "2-ICT项目融CT产品报表_南京市_20250125.xlsx";
        DemoDataListener<Entity2> dataResultListener = new DemoDataListener<>();
        EasyExcel.read(entityPath2, Entity2.class,dataResultListener).sheet().doRead();
        return dataResultListener.getDataList();
    }

    public Map<String,Entity5> getEntity5Map() {
        List<Entity5> entity5List = getEntity5List();
        Map<String,Entity5> map = new HashMap<>();
        for (Entity5 entity5 : entity5List) {
            map.put(entity5.getProjectCode(),entity5);
        }
        return map;
    }

    /**
     * 获取“项目时效报表”中所有的数据
     * @return {@code List<Entity5> }
     */
    public List<Entity5> getEntity5List() {
        String entityPath5 = path + "5-项目时效报表.xlsx";
        DemoDataListener<Entity5> dataResultListener = new DemoDataListener<>();
        EasyExcel.read(entityPath5, Entity5.class,dataResultListener).sheet().doRead();
        return dataResultListener.getDataList();
    }

    public Map<String,Entity6> getEntity6Map() {
        List<Entity6> entity6List = getEntity6List();
        Map<String,Entity6> map = new HashMap<>();
        for (Entity6 entity6 : entity6List) {
            map.put(entity6.getContractId(),entity6);
        }
        return map;
    }

    public List<Entity6> getEntity6List() {
        String entityPath6 = path + "6-合同明细报表.xlsx";
        DemoDataListener<Entity6> dataResultListener = new DemoDataListener<>();
        EasyExcel.read(entityPath6, Entity6.class,dataResultListener).sheet().doRead();
        return dataResultListener.getDataList();
    }

    private static BigDecimal toBigDecimal(Double value) {
        return value == null ? BigDecimal.ZERO : new BigDecimal(value);
    }
}
